package com.lvmama.pet.sync.service.util;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.lvmama.comm.bee.service.sync.SyncBaseExecuteService;

public class JDBCUtils {
	private static final long DEFAULT_DB_SYNC_SECOND = 2L;

	static final String REGX = "#[A-Za-z0-9_\\.]+#";
	
	static final Pattern REGX_PATTERN = Pattern.compile(REGX);
	
	static final Log LOG = LogFactory.getLog(JDBCUtils.class);
	
	static final Properties jdbcProp = new Properties();
	static{
		try{
			//jdbcProp.load(JDBCUtils.class.getResourceAsStream("/jdbc.properties"));			
		}catch(Exception e){
			LOG.error("load properties from jdbc.properties failed",e);
		}		
	}
	
	public static List<String> createParamKeyList(String sql) {
		List<String> list = new ArrayList<String>();
		Matcher matcher = REGX_PATTERN.matcher(sql);
		while (matcher.find()) {
			list.add(matcher.group().replace("#", ""));
		}
		return list;
	}

	public static String createPrepareStatementSql(String querySql) {
		return querySql.replaceAll(REGX, "?");
	}

	public static List<String> getColumnNames(ResultSet rs) throws SQLException {
		List<String> list = new ArrayList<String>();
		for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
			list.add(rs.getMetaData().getColumnName(i + 1));
		}
		return list;
	}

	@SuppressWarnings("rawtypes")
	public static void setPreparedStatementParams(List<String> paramKeyList,
			PreparedStatement ps, Map param) throws SQLException {
		for (int i = 0; i < paramKeyList.size(); i++) {
			Object v = param.get(paramKeyList.get(i));
			if (v == null) {
				ps.setNull(i + 1, Types.VARCHAR);
			} else {
				if (v instanceof String) {
					ps.setString(i + 1, v.toString());
				} else if (v instanceof Integer) {
					ps.setInt(i + 1, (Integer) v);
				} else if (v instanceof Long) {
					ps.setLong(i + 1, (Long) v);
				} else if (v instanceof Double) {
					ps.setDouble(i + 1, (Double) v);
				} else if (v instanceof Float) {
					ps.setFloat(i + 1, (Float) v);
				}else if (v instanceof BigDecimal) {
					ps.setBigDecimal(i + 1, (BigDecimal) v);
				} else if (v instanceof Timestamp) {
					ps.setTimestamp(i + 1, (Timestamp) v);
				} else if (v instanceof Date) {
					ps.setDate(i + 1, new java.sql.Date(((Date)v).getTime()));
				} else if (v instanceof java.sql.Date) {
					ps.setDate(i + 1, (java.sql.Date)v);
				} else {
					ps.setString(i + 1, v.toString());
				}
			}
		}
	}

	public static List<Map<String, Object>> catchResult(ResultSet rs) throws SQLException {
		List<String> columnNames = JDBCUtils.getColumnNames(rs);
		rs.getMetaData().getColumnCount();
		List<Map<String, Object>> result = new ArrayList<Map<String,Object>>();
		while (rs.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (String column : columnNames) {
				Object v = rs.getObject(column);
				if (v != null) {
					String className = v.getClass().getName();
					if ("oracle.sql.TIMESTAMP".equals(className)) {
						oracle.sql.TIMESTAMP timestamp = (oracle.sql.TIMESTAMP) v;
						java.sql.Timestamp tsp = timestamp.timestampValue();
						map.put(column, tsp);
					} else {
						map.put(column, v);
					}
				}
			}
			result.add(map);
		}
		return result;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public static Map createMapParamByObject(List<String> paramKeyList, Object param,
			String sourceId) throws Exception {
		if (param instanceof Map) {
			Map par = (Map) param;
			par.put(SyncBaseExecuteService.PK_VALUE_KEY, sourceId);
			return par;
		}
		Map par = new HashMap();
		for (String key : paramKeyList) {
			Object v = catchObjectValue(param, key);
			if (v != null) {
				par.put(key, v);
			}
		}
		return par;
	}

	private static Object catchObjectValue(Object param, String key) throws Exception {
		String[] keys = key.split("\\.");
		return getReflectValue(param, keys, 0);
	}
	
	private static Object getReflectValue(Object param, String[] keys, int i) throws Exception {
		if (param == null) {
			return null;
		}
		if (keys.length == i) {
			return param;
		}
		String key = keys[i];
		Method getter = param.getClass().getDeclaredMethod("get" + key.substring(0, 1).toUpperCase() + key.substring(1));
		Object v = getter.invoke(param);
		return getReflectValue(v, keys, i + 1);
	}
	
	public static Long getDBSyncSecond(){
		Long dbSyncSecond=DEFAULT_DB_SYNC_SECOND;
		
		String dbSyncSecondStr=jdbcProp.getProperty("DB_SYNC_SECOND");
		if(!StringUtils.isEmpty(dbSyncSecondStr)){
			try{
				dbSyncSecond=Long.parseLong(dbSyncSecondStr);				
			}catch(Exception e){
				LOG.error("parse dbSyncSecond error",e);
			}
		}
		
		return dbSyncSecond;
	}

}